import { prismaSqlUtils } from "../utils/prisma.sql.utils";

export class AdminService {
  async stats() {
    let sql = `SELECT 
            -- 博客相关统计
            blog_current.month AS blog_current_month,
            blog_current.count AS blog_current_count,
            blog_prev.count AS blog_prev_count,
            -- 计算博客增长率 (避免除以零)
            CASE 
                WHEN blog_prev.count = 0 THEN 
                    IF(blog_current.count > 0, 100, 0)  -- 上月为0，本月有新增则增长率100%
                ELSE 
                    ROUND((blog_current.count - blog_prev.count) / blog_prev.count * 100, 2) 
            END AS blog_growth_rate,
            
            -- 用户相关统计
            user_current.count AS user_current_count,
            user_prev.count AS user_prev_count,
            -- 计算用户增长率
            CASE 
                WHEN user_prev.count = 0 THEN 
                    IF(user_current.count > 0, 100, 0)  -- 上月为0，本月有新增则增长率100%
                ELSE 
                    ROUND((user_current.count - user_prev.count) / user_prev.count * 100, 2) 
            END AS user_growth_rate

        -- 当月博客统计
        FROM (
            SELECT 
                DATE_FORMAT(CURDATE(), '%Y-%m') AS month,
                COUNT(*) AS count
            FROM blog
            WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')  -- 当月第一天
                AND created_at < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)  -- 下月第一天
        ) AS blog_current

        -- 上月博客统计
        LEFT JOIN (
            SELECT 
                DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') AS month,
                COUNT(*) AS count
            FROM blog
            WHERE created_at >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')  -- 上月第一天
                AND created_at < DATE_FORMAT(CURDATE(), '%Y-%m-01')  -- 当月第一天
        ) AS blog_prev ON 1=1

        -- 当月用户统计
        LEFT JOIN (
            SELECT 
                COUNT(*) AS count
            FROM user
            WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
                AND created_at < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
        ) AS user_current ON 1=1

        -- 上月用户统计
        LEFT JOIN (
            SELECT 
                COUNT(*) AS count
            FROM user
            WHERE created_at >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                AND created_at < DATE_FORMAT(CURDATE(), '%Y-%m-01')
        ) AS user_prev ON 1=1`;

    let result = await prismaSqlUtils.queryOne<any>(sql);
    const userCount = await prismaSqlUtils.queryOne(
      "select count(id) as user_count from user"
    );
    const blogCount = await prismaSqlUtils.queryOne(
      "select count(id) as blog_count FROM blog"
    );

    const data = {
      userCount: userCount.user_count.toString() as string,
      blogCount: blogCount.blog_count.toString() as string,
      newThisMonth: result.blog_current_count.toString() as string,
      userGrowth: result.user_growth_rate.toString() as string,
      blogGrowth: result.blog_growth_rate.toString() as string,
    };
    return data;
  }
}
